package com.ajiao3.preparedstatement.crud;

import com.ajiao.Connection.ConnectionTest;
import com.util.JDBCUtil;
import org.junit.Test;

import javax.rmi.CORBA.Util;
import java.io.IOException;
import java.io.InputStream;
import java.net.SocketTimeoutException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

/**
 * 使用perparedStatement来替换Statement,实现对数据库的增删改查
 */
public class preparedStatementTset {
    @Test
    public  void  testCommonUpdate(){
//        String sql="delete from customers where id = ?";
//        update(sql,20);
        String sql ="update user set name = ? , password = ? where id = ?";
        update(sql,"阿娇",123,5);
    }

    /**
     * 通用的增删改操作
     */
    //sql语句中的占位符的个数与可变形参的长度相同
    public void update(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取连接
            conn = JDBCUtil.getConnection();
            //2.预编译,返回preparedStatement的实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);//占位符是从1开始的 所以i+1
            }
            //4.执行
            boolean execute = ps.execute();
            if (!execute)
                System.out.println("操作成功！");
            else
                System.out.println("操作失败!");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtil.CloseResource(conn, ps);
        }

    }


    /**
     * 修改数据
     */
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            //1.获取数据库连接
            conn = JDBCUtil.getConnection();
            //2.预编译sql语句，返回preparedStatement的实例
            String sql = "update customers set name = ? where id = ?";
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            ps.setString(1, "哈尔温");
            ps.setObject(2, 19);
            //4.执行
            boolean execute = ps.execute();
            if (!execute)
                System.out.println("修改成功！");
            else
                System.out.println("修改失败!");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtil.CloseResource(conn, ps);
        }

    }


    /**
     * 添加数据
     */
    @Test
    public void testInsert() {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = JDBCUtil.getConnection();

            //4.预编译sql语句，返回prepareStatement的实例
            String sql = "insert into customers(name,email,birth)values (?,?,?)";
            ps = conn.prepareStatement(sql);

            //5.填充占位符
            ps.setString(1, "王五二");
            ps.setString(2, "111qq@.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("1002-01-02");
            ps.setDate(3, new Date(date.getTime()));

            //6.执行sql操作
            boolean execute = ps.execute();
            if (!execute)
                System.out.println("插入成功！");
            else
                System.out.println("插入失败!");


        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.CloseResource(conn, ps);
        }

    }
}
